package com.zhiyou100.servlet;

import cn.hutool.core.lang.Console;
import com.sun.org.apache.regexp.internal.RE;
import com.zhiyou100.dao.StudentDao;
import com.zhiyou100.entity.Student;
import lombok.SneakyThrows;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
import java.util.List;

/**
 * 读取 Excel,写入的数据库
 *
 * @author yang
 * @version 1.0.0
 * @date 2020-09-15 16:39
 */
@WebServlet(name = "StudentReadExcel", urlPatterns = "/student/addList")
public class StudentReadExcel extends HttpServlet {
    @SneakyThrows
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        // 获取表单解析器工厂对象
        final DiskFileItemFactory factory = new DiskFileItemFactory();
        // 创建解析器对象
        final ServletFileUpload upload = new ServletFileUpload(factory);
        // 调用解析器的 parse 方法解析 request 对象
        final List<FileItem> list = upload.parseRequest(req);
        // 只有一个组件
        final FileItem fileItem = list.get(0);
        // 获取提交文件的输出流
        final HSSFSheet sheet;
        try (InputStream in = fileItem.getInputStream()) {
            // 创建工作簿，关联源文件
            try (HSSFWorkbook book = new HSSFWorkbook(in)) {
                // 获取第一个 工作簿
                sheet = book.getSheetAt(0);
            }
        }

        // 从第一行（省略第0行的标题）开始遍历
        for (int j = 1; j < sheet.getPhysicalNumberOfRows(); j++) {
            final HSSFRow row = sheet.getRow(j);
            // 创建一个 student 对象，放从表中提取的数据
            final Student student = new Student();
            // 遍历每行的单元格
            for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
                // 获取 i 下标下的单元格
                final Cell cell = row.getCell(i);
                // 判断单元格的数据的类型
                switch (cell.getCellTypeEnum()) {
                    // number 类型，包括 整数、小数、日期
                    case NUMERIC:
                        // 判断是不是日期
                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                            final Date dateCellValue = cell.getDateCellValue();
                            student.setSbirthday(dateCellValue);
                            // 不是日期就是 整数或者小数
                        } else {
                            final double numericCellValue = cell.getNumericCellValue();
                            // 单元格下标0 是 sid，2 是 sage， 3 是score
                            switch (i) {
                                case 0:
                                    student.setSid((int) numericCellValue);
                                    break;
                                case 2:
                                    student.setSage((int) numericCellValue);
                                    break;
                                case 3:
                                    student.setScore((float) numericCellValue);
                                    break;
                                default:
                            }
                        }
                        break;
                    // 字符串或者文本类型
                    case STRING:
                        final String stringCellValue = cell.getStringCellValue();
                        student.setSname(stringCellValue);
                        break;
                    default:
                }
            }
            Console.log(student);
            // 插入到数据数据库中
            new StudentDao().insertOne(student);
        }


        //请求字符到请求页面
        req.setCharacterEncoding("UTF-8");
        req.setAttribute("message", "上传成功！");
        req.getRequestDispatcher("/index.jsp").forward(req, resp);

    }

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        this.doPost(req, resp);
    }
}
